Excel Iteration DLL

Legal Disclaimer, Trademark, Copyright and Files

About Requirements, Problems and the Author

What is an Iteration?

Installation

Loading ITERATE.XLL

Tabulation Area

Description Area

Iteration Dialog Box

Examples

Modifying ITERATE.XLA


Iterate Dialog

This section describes the fields of the input dialog and what they are for.

Description	Place the cell range of your description area in this control. Remember that it must have at least two rows and one column for each cell you want to iterate.

Steps	The value in this control determines how often the iteration will run.

You must supply values for the parameters above. The others are optional. Mark the corresponding checkbox if you want to apply one of the optionals.

Table at	Normally the tabulation area starts three rows under the current description area. You can move the tabulation area to a different place by placing the cell address of the upper-left edge of the area in the control.

Apply Macro	The iteration function can execute a macro after recalculation of the sheets. This macro should take no parameters; it's called without any. Place the address of the macro in the control and mark the checkbox to do this.

Stop Trigger	Working without a stop trigger means that the iteration runs the amout of loops you give in the steps control. But you can change this behavior and tell the iteration function that it has to stop if a certain cell (the stop trigger) returns a non-zero value.

Goto Last Line	Setting this checkbox tells the iteration function to move the cell cursor to the last row of the iteration table when the iteration stops. Normally the cursor is positioned on the upper-left edge of this table.

Test Input	If you set this option the iteration function tests the input cells in the tabulation area if the cells are empty or not and stops if an empty cell was found.


Description Area
The Description Area

Every iteration needs a description that tells the function which cell you want to tabulate or iterate and which cells are input cells to your scheme. This is done with the description area. It must have at least two rows and one column for each cell that is part of your iteration.

The first row names the input and the second the output cells.

E.g. look at the following description area:

	A4		D8
		B4	C7	H3

The first column of the description area defines the first column of the tabulation area as input column with the input cell A4. You should supply values for it.

The second and the fourth column are output columns; B4 and H3 are output cells. The iteration fills the corresponding cells in the tabulation area with the values of that cells.

The third column describes an iteration. The value of C7 in one iteration step is tabulated and gives the input value for D8 for the next step.

As the example above shows you'll have to supply cell addresses in the description area.

	A4	this is a valid cell address.
	=A4	This is a formula returning the value of the cell A4. It won't work in most cases but see below.
	="A4"	This is also a formula but this one returns a string which is a valid cell address. This one will work. So if in the formula above the cell A4 returns a valid cell address it's ok but it doesn't tabulate the cell A4.

You can't use names in the description area. So if one of your iteration cells has the name 'xyz' you can't use 'xyz' as substitute for the cell address.


Tabulation Area
The Tabulation Area

The iteration function needs a rectangular cell array where it can read input values and place the output values of the iteration steps; the tabulation area. Each column in this array corresponds with a column in the description area.

Suppose you have the following description area:

	A4		D8
		B4	C7	H3

then your tabulation area could look like this:

	1		39
	2
	3
	4
	5
The first column is filled with value because the first column of the description area describes it as input column with A4 as input cell. So you need one value for each iteration step.

The third column contains only one value (an initial value) because this one is an iterative column. The value of the kth-step is placed in the input cell D8 and the value for the (k+1)th-step is the value of C7 in the kth. The (output) values of C7 are tabulated in this column.

You can ommit values in output columns.


Iterations
What is an Iteration?

Suppose you have an Excel sheet and you want to place several value in some cells and tabulate the values of some other cells with respect to the input values. This is the basic idea behind the iteration library.

Let's take a very simple example (see examp1.xls):

		A	B	C
	1
	2		=A2*A2
	3
	4

The formula in B2 computes the square of the value of A2; that's all (yes this example is very simple). Suppose further you want to tabulate the value of B2 with respect to A2.

To do that with the iteration function you should type the following into your sheet:

		D	E	F	G
	1
	2		A2
	3			B2
	4
	5		-1
	6		0
	7		1
	8		2
	9		3
	10		4
Select 'Iterate' from the Excel formula menu give E2:F3 as description area and set the steps value to 6. Press ok.

What happens then is that the iterate function takes every of the input values places it in the corresponding input cell and copies the value of the output cell(s) into the corresponding output column.

As you see iterations are nothing really difficult but it's a thing that Excel can't do (I'm not sure about the new Version 5.0 but Excel 4.0 couldn't).

See also the example files.


Installation

Installation of the ITERATE.XLL library is very simple. Copy the files ITERATE.XLL and ITERATE.XLA in any directory you like. The only thing is that you must copy both files into the same directory.

Copy ITERATE.HLP to any directory named in your PATH Variable, or to your Windows Directory or to your Excel directory.

Keep the file ITERATE.XLM on your system for modifying ITERATE.XLA

Do what ever you want with the example files.


Loading ITERATE.XLL

There are two ways to load ITERATE.XLL into Microsoft Excel. You can use the File Open Command from the Excel Menu to load it in a particular Excel session. You may also use the Excel's Add-In Manager to load the library any time you start Excel.

ITERATE.XLL appends two new menu items to the Formula menu: one seperator and the Iterate item. This is bound to the iterate function of the library. If you unload ITERATE.XLL these menu items aren't removed because this could cause problems if other libraries or macro sheet have modified this menu in the meantime. If you want to change this you must modify ITERATE.XLA.

The library also assigns <Ctrl+i> as an accelerator to the iterate function.


Modifying ITERATE.XLA

There are two reasons for modifying the ITERATE.XLA file: one is to change the menu behaviour of the iterate function and the other is to edit the iterate dialog to support your national language. There are several notes in the files describing the various components in the file (if you open it to see how the iterate function works you won't see much because only a minimal functionality has been implemented into the macro sheet).

Follow these steps:

	1.	To modify ITERATE.XLA first make sure that this file isn't loaded. To do this you should unload ITERATE.XLL because the library loads the macro sheet automatically.
	2.	Open ITERATE.XLM.
	3.	Make the changes you want. The function install() appends the Iterate menu item to the formula menu and uninstall() removes it. To remove the Iterate item when you unload ITERATE.XLL unqoute the two macro commands. Goto the text section in the dialog box (it's name ist iterateDLG) and edit it for your language. Do not make other changes to the file.
	4.	Save ITERATE.XLM.
	5.	Save ITERATE.XLM as ITERATE.XLA but make sure that you save it as international integrated macro sheet.
	6.	Close the file (it's new name should be now ITERATE.XLA).
Ready.


Example Files

There are some examples for the iteration library in the archive:

	EXAMP1.XLS	The very simple example I used to explain what iteration are.
	EXAMP2.XLS	This sheet computes the root of any positive number using the Heron Iteration. It shows also how stop triggers work.
	EXAMP3A.XLS EXAMP3B.XLS	EXAMP3A.XLS contains a 10-step Heron iteration for finding the root of a positive number and EXAMP3B.XLS contains the iteration parameters. This is the sort of calculations I wrote the library for because a complete n-step Heron iteration can't be computed in a closed formula. It also shows how to apply iterations across different sheets.


Legal Disclaimer, Trademark and Copyright
Legal Disclaimer, Trademarks and Copyright

Ok, this is most probably not what you really want but you should read this chapter too.

Legal Disclaimer	The author of the iterate (Wolfgang Zekoll) is not liable or responsible to any person or entity with respect to any loss or damages arising from the usage of the iterate package.  That means I do not guarentee that the files in the archive work correct or as stated as in this documentation. I do not guarentee the correctness of the result.

You use the files in the package completly on your own risk.

Trademarks	Microsoft is a registered trademark. Windows and Microsoft Excel is a registered trademark of Microsoft Corporation.

The fact that a name is neither listed above nor marked as a trademark does not mean that this isn't a (registered) trademark.

Copyright	The iterate package is freeware but keep in mind that it is still copyrighted material; all rights are reserved.	You can give as much copies to others as you like. You can take a distribution fee as long as this fee does not exceed $5 (five U.S. Dollar) or the equivalent in other currencies.

If you give copies to others you must give them the complete unmodified package. You may modify the file ITERATE.XLA as described in this documentation for your own use. Contact me if you want to redistribute ITERATE.XLA in another language.

Files	You should have found the following files in the archive:

	ITERATE.XLL	The macro library.
	ITERATE.XLA	Excel macro sheet; used by ITERATE.XLL.
	ITERATE.XLM	Excel macro sheet; use it as template for your own version of ITERATE.XLA.
	ITERATE.HLP	This help file.
	EXAMP1.XLS EXAMP2.XLS EXAMP3A.XLS EXAMP3B.XLS	Some example files.
	README.TXT	A short readme file.


Requirements, Problems and the Author

Requirements	What you need to use the iteration package is Microsoft Windows and Excel 4.0 (or higher).  Other things you should bring are iterative problems for Excel.

Problems	Altough I've tried to be careful I'm not sure if the library works with any other version of Microsoft Excel than a German one. Try it.

Other problems are that you can't use cell names inside the description area and that the iteration function can't be integrated into macros. But these two things will probably be changed in a future version.

The Library	ITERATE.XLL was compiled using Microsoft C7, Microsoft Excel Software Development Kit, lots of tobacco and coffee and a considerable amout of time. The documentation has been made with Lotus Ami Pro 3.0 for Windows and a supporting macro package.

It's my first library for Excel so don't expect too much perfection from it.

The Author	The author Wolfgang Zekoll can be reached via email; use the address a2346710@athena.rrz.uni-koeln.de for this.

If you can't reach me via email or if I don't respond for a week or two send a fax to Germany-02234/22984 (most probably you'll have to drop the '0' infront of the '2234').

Feel free to contact me for any reason; I'm interested in any feedback.